# 学生数据库交互类
import pymysql


class student_dal:
    """
    学生数据库交互类
    对学生信息表初始化（没有则创建）
    增加，查询，删除信息
    """

    def __init__(self):
        self.dbconnection = pymysql.connect(
            host="localhost", user="appuser", password="123456", db="mydb", charset="utf8")
        self.cursor = self.dbconnection.cursor()
        # # 是否存在学生表，不存在则创建
        # createTableSQL = "create table if not exists student(id int unsigned not null auto_increment primary key,name varchar(32) not null unique,age int unsigned not null,classid varchar(200) null) engine=InnoDB;"
        # self.cursor.execute(createTableSQL)

    def findAll(self):
        """
        返回所有的学生信息列表
        """
        findAllSql = "select * from student order by id asc"
        self.cursor.execute(findAllSql)
        stuList = []
        for stu in self.cursor.fetchall():
            obj = {}
            obj["id"], obj["name"], obj["age"], obj["classid"] = stu
            stuList.append(obj)
        return stuList
        # return self.cursor.fetchall()

    def create(self, student):
        """
        创建学生记录
        """
        try:
            createSql = "insert into student(name,age,classid) values ('{0}',{1},'{2}')".format(
                student['name'], student['age'], student['classid'])
            self.cursor.execute(createSql)
            self.dbconnection.commit()
        except:
            print("新建学生时发生错误，请检查学生对象的字段值是否合法！")

    def delete(self, stuId):
        """
        删除指定记录
        """
        try:
            deleteSql = "delete from student where id={0}".format(stuId)
            self.cursor.execute(deleteSql)
            self.dbconnection.commit()
        except:
            print("输入的学员号有错误！")

    def __del__(self):
        """
        释放数据库连接
        """
        self.dbconnection.close()
        # print("释放了数据库连接")


if __name__ == "__main__":
    # 测试
    dal = student_dal()
    dal.create({"name": "zhangsan", "age": 13, "classid": "python22"})
    for stu in dal.findAll():
        print(stu)
    dal.delete(1)
